//
//  PpFmdbTools.swift
//  Palmpilot
//
//  Created by 杨伟毅 on 2018/10/30.
//  Copyright © 2018年 杨伟毅. All rights reserved.
//

import UIKit
import FMDB
import QorumLogs

class PpFmdbTools: NSObject {
    static let shared:PpFmdbTools = PpFmdbTools()
    var database: FMDatabase!
    
    private override init() {
        super.init()
        // 创建
        let ZJCacheStr = getFileFullPathWithFileNmae(fileName: "PpCache.db")
        //        database = FMDatabaseQueue.init(path: ZJCacheStr)
        database = FMDatabase.init(path: ZJCacheStr)
        // open 打开
        if database.open() {
            creatZJVedioCache()
        }else{
            print("dabase open error:\(database.lastError())")
        }
    }
    func getFileFullPathWithFileNmae(fileName:String)  ->String {
        // 直接获取Documents的路径
        let documentStr = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first
        return  documentStr!.appending("/\(fileName)")
    }
    // 创建表格
    func creatZJVedioCache() -> Void {
        let creatTableSQl = "CREATE TABLE IF NOT EXISTS Vedio (titleStr,contentStr,dateStr,Ppname)"
        //        database.inDatabase { (db) in
        let isResult = database.executeUpdate(creatTableSQl, withArgumentsIn: [""])
        if isResult {
            // 创建成功
            print("创建表格成功")
        }else{
            print("创建表格失败")
        }
    }
    func insterModel(model:PpBaseModel) -> Void {
        if queryDataById(baseModel: model) == true {
            let  sqlStr = "UPDATE Vedio SET titleStr=? WHERE contentStr=?"
            //            database.inDatabase { (db) in
            let isSuccesSlly = database.executeUpdate(sqlStr, withArgumentsIn: [model.titleString,model.contentString])
            if !isSuccesSlly {
                print("update  error     FMDB    + ++ ++ \(String(describing: database?.lastError()!))")
            }
        }else{
            // 不判断是否存在  没更改一次 就会插入更新
            let sql = "INSERT INTO Vedio (titleStr,contentStr,dateStr,Ppname) VALUES(?,?,?,?)"
            let isSuccesully = database?.executeUpdate(sql, withArgumentsIn:[model.titleString,model.contentString,model.baseDate,"Ppname"])
            if !isSuccesully! {
                print("inster error     FMDB    + ++ ++ \(String(describing: database?.lastError()!))")
            }
        }

    }
    // 根据时间和名字判断是否存在
    func queryDataById(baseModel:PpBaseModel) -> Bool {
        var isExit = Bool()
        //        database.inDatabase { (db) in
        let sqlStr = "select * from Vedio where dateStr = ?"
        let result:FMResultSet = database!.executeQuery(sqlStr, withArgumentsIn: [baseModel.baseDate,baseModel.titleString])!
        if result.next() {
            isExit = true
        }else{
            isExit = false
        }
        //        }
        return isExit
    }
    
    
    // 根据时间删除数据
    func delegateModel(model:PpBaseModel) ->Void{
        let sql = "delete  from Vedio where  dateStr = ?"
        let  isOK = database.executeUpdate(sql, withArgumentsIn: [model.baseDate])
        if isOK {
            debugPrint("数据删除成功")
        }
    }
    
    // (titleStr,contentStr,dateStr 根据Ppname主键查询数据
    func readVedioIdReturn(dateStr:String) -> [PpBaseModel] {
        let sql = "select * from Vedio where  Ppname = ?"
        var  arr = [PpBaseModel]()
        let rs =  database?.executeQuery(sql, withArgumentsIn: [dateStr])
        while  (rs?.next())! {
            let baseModel = PpBaseModel()
            baseModel.titleString = (rs?.string(forColumn: "titleStr"))!
            baseModel.contentString = (rs?.string(forColumn: "contentStr"))!
            baseModel.baseDate = (rs?.string(forColumn: "dateStr"))!
            arr.append(baseModel)
        }
        return arr
    }
    func readDateStrReturn(dateStr:String,titleStr:String) -> [PpBaseModel] {
        let sql = "select * from Vedio where   dateStr = ? and titleStr = ?"
        var  arr = [PpBaseModel]()
        let rs =  database?.executeQuery(sql, withArgumentsIn: [dateStr,titleStr])
        while  (rs?.next())! {
            let baseModel = PpBaseModel()
            baseModel.titleString = (rs?.string(forColumn: "titleStr"))!
            baseModel.contentString = (rs?.string(forColumn: "contentStr"))!
            baseModel.baseDate = (rs?.string(forColumn: "dateStr"))!
            arr.append(baseModel)
        }
        return arr
    }
    
    
}
